package com.house.java.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.house.java.dao.HouseSurroundDAO;
import com.house.java.model.HouseSurround;
import com.house.java.util.DBUtil;

public class HouseSurroundDaoImpl implements HouseSurroundDAO {
	
	private static Logger log = Logger.getLogger(HouseSurroundDaoImpl.class);

	public void insertHouseSurround(HouseSurround houseSurround) {
		String sql = "insert into " +
				"house_surround(" +
				"kindergarten,primarySchool,middleSchool," +
				"highMiddleSchool,university,hospital," +
				"bank,plaza,park) " +
				"values(?,?,?,?,?,?,?,?,?)";
		Connection conn = DBUtil.getConnection();
		try {
			if(conn.getAutoCommit()){
				conn.setAutoCommit(false);
			}
		} catch (Exception e) {
			log.error("获取数据库连接是否自动提交发生异常");
		}
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			int parameterIndex = 1;
			pst.setString(parameterIndex++, houseSurround.getKindergarten());
			pst.setString(parameterIndex++, houseSurround.getPrimarySchool());
			pst.setString(parameterIndex++, houseSurround.getMiddleSchool());
			pst.setString(parameterIndex++, houseSurround.getHighMiddleSchool());
			pst.setString(parameterIndex++, houseSurround.getUniversity());
			pst.setString(parameterIndex++, houseSurround.getHospital());
			pst.setString(parameterIndex++, houseSurround.getBank());
			pst.setString(parameterIndex++, houseSurround.getPlaza());
			pst.setString(parameterIndex++, houseSurround.getPark());
			pst.executeUpdate();
			ResultSet rs = pst.getGeneratedKeys();
			if(rs.next()){
				Long id = rs.getLong(1);
				houseSurround.setId(id);
			}
			rs.close();
			pst.close();
			conn.commit();
		} catch (Exception e) {
			log.error(e.getMessage());
			try {
				conn.rollback();
			} catch (Exception e2) {
				log.error(e2.getMessage());
			}
		}finally{
			try {
				conn.close();
			} catch (Exception e2) {
				log.error(e2.getMessage());
			}
		}
	}

	public int delete(Long id) {
		int result = -1;
		Connection conn = DBUtil.getConnection();
		try {
			if(conn.getAutoCommit()){
				conn.setAutoCommit(false);
			}
		} catch (Exception e) {
			log.error("获取数据库连接是否自动提交发生异常");
		}
		try {
			String sql = "delete from house_surround where id = ?";
			PreparedStatement pst = conn.prepareStatement(sql);
			int parameterIndex = 1;
			pst.setLong(parameterIndex, id);
			// 获取刚刚插入的记录的主键，设置到house对象中
			result = pst.executeUpdate();
			pst.close();
			conn.commit();
		} catch (Exception e) {
			log.error(e.getMessage());
			try {
				conn.rollback();
			} catch (SQLException e1) {
				logConnectionRollbackException();
				log.error(Thread.currentThread().getId()+" "+e1.getMessage());
			}
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logConnectionCloseExceptionFunction();
				log.error(Thread.currentThread().getId()+" "+e.getMessage());
			}
		}
		return result;
	}

	
	private static void logConnectionCloseExceptionFunction(){
		log.error(Thread.currentThread().getId()+" close Connection appear an Exception");
	}
	private static void logConnectionRollbackException(){
		log.error(Thread.currentThread().getId()+" rollback Connection appear an Exception");
	}

	public List<HouseSurround> queryById(Long id) {
		List<HouseSurround> surrounds = new ArrayList<HouseSurround>();
		Connection conn = DBUtil.getConnection();
		StringBuffer sb = new StringBuffer();
		sb.append("select * from second_house where 1=1 ");
		StringBuffer sbCount = new StringBuffer();
		sbCount.append("select count(*) from second_house where 1=1 ");
		if(null != id){
			sb.append(" and id=? ");
			sbCount.append(" and id=? ");
		}
		try {
			PreparedStatement pst = conn.prepareStatement(sb.toString());
			PreparedStatement pstCount = conn.prepareStatement(sbCount.toString());
			pst.setLong(1, id);
			pstCount.setLong(1, id);
			log.info(pst.toString());
			ResultSet result = pst.executeQuery();
			HouseSurround surround;
			while(result.next()){
				surround = new HouseSurround();
				surround.setId(result.getLong("id"));
				surround.setBank(result.getString("bank"));
				surround.setHighMiddleSchool(result.getString("highMiddleSchool"));
				surround.setHospital(result.getString("hospital"));
				surround.setKindergarten(result.getString("kindergarten"));
				surround.setMiddleSchool(result.getString("middleSchool"));
				surround.setPark(result.getString("park"));
				surround.setPlaza(result.getString("plaza"));
				surround.setPrimarySchool(result.getString("primarySchool"));
				surround.setUniversity(result.getString("university"));
				surrounds.add(surround);
			}
			result.close();
			pst.close();
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e.getMessage());
		}finally{
			try {
				conn.close();
			} catch (Exception e2) {
				logConnectionCloseExceptionFunction();
				log.error(e2.getMessage());
			}
		}
		return surrounds;
	}
}
